package com.lee.jdbc.dao.trans;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.lee.jdbc.StudentMarks;
import com.lee.jdbc.mappers.StudentMarksMapper;

public class StudentJdbcTemplate implements StudentDao {
	
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;
	private PlatformTransactionManager transactionManager;
	
	@Override
	public void setDataSource(DataSource ds) {
		dataSource = ds;
		jdbcTemplateObject = new JdbcTemplate(ds);
	}

	public void setTransactionManager(PlatformTransactionManager transactionManager) {
		this.transactionManager = transactionManager;
	}
	
	@Override
	public void create(String name, int age, int marks, int year) {
		TransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = transactionManager.getTransaction(def);
		try {
			String sql = "insert into Student(name,age) values (?,?)";
			jdbcTemplateObject.update(sql, name, age);
			String sql2 = "select max(id) from Student";
			int sid = jdbcTemplateObject.queryForObject(sql2, int.class);
			String sql3 = "insert into Marks(sid,marks,year) values (?,?,?)";
			jdbcTemplateObject.update(sql3, sid, marks, year);
			System.out.println("Created Name = "+name+", Age = "+age);
			transactionManager.commit(status);
		}catch (DataAccessException e) {
			System.err.println("Error in creating record, rolling back");
			transactionManager.rollback(status);
			throw e;
		}
	}

	@Override
	public List<StudentMarks> listStudents() {
		String sql = "select * from Student,Marks where Student.id=Marks.sid";
		List<StudentMarks> studentMarks = jdbcTemplateObject.query(sql, new StudentMarksMapper());
		return studentMarks;
	}

}
